﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SQLite;
using System.Xml.Linq;
using FixtureManageSystem.Views;
using MySql.Data.MySqlClient;
using MySqlX.XDevAPI.Common;


namespace FixtureManageSystem.Models
{
    public class BorrowService
    {

        public SqliteHelper sqliteHelper = new SqliteHelper();
        /// <summary>
        /// 提交借用单，有可能借用失败？比如借东西的数量超过库内存有的数量
        /// </summary>
        /// <param name="borrow"></param>
        /// <returns></returns>
        public bool Add(Borrow borrow)
        {

            // 借用一次，就产生一张借用单据
            string str;
            if (borrow.Status == "借用")
            {
                str = string.Format(
                @"UPDATE Fixture SET Num = Num - {2} WHERE BarCode = '{0}';
                INSERT INTO ReturnFixture(BarCode, PersonNo, Num, UpdateTime, Comment, Status) 
                VALUES ('{0}', '{1}', {2}, '{3}', '{4}', '借用');
                ", borrow.BarCode, borrow.PersonNo, borrow.Num, borrow.UpdateTime.ToString("yyyy-MM-dd"), borrow.Comment);
            }
            // 归还一次，也产生一次单据
            else
            {
                str = string.Format(
                @"UPDATE Fixture SET Num = Num + {2} WHERE BarCode = '{0}';
                INSERT INTO ReturnFixture(BarCode, PersonNo, Num, UpdateTime, Comment, Status) 
                VALUES ('{0}', '{1}', {2}, '{3}', '{4}', '归还');",
                    borrow.BarCode, borrow.PersonNo, borrow.Num, borrow.UpdateTime.ToString("yyyy-MM-dd"), borrow.Comment
                );
            }

            // 3、执行SQL语句并接受结果
            int r = sqliteHelper.Execute(str);
            return r > 0;
        }


        /// <summary>
        /// 是否存在物料编码为 XXX的物料
        /// </summary>
        /// <returns></returns>
        public bool IsExists(string BarCode)
        {
            string str = $"SELECT * FROM fixture WHERE BarCode='{BarCode}'";
            // 打开数据库
            SQLiteDataReader Sdr = sqliteHelper.read(str);
            while (Sdr.Read())
            {
                return true;
            }
            return false;
        }

        // 获取借用记录总数1
        public int GetBorrowCount()
        {
            // 创建一变量来存储返回值
            int count;
            // 创建要执行的Sql 语句
            string str = "SELECT COUNT(*) as totalNumber FROM ReturnFixture";
            // 2、执行SQL语句并接受结果
            SQLiteDataReader Sdr = sqliteHelper.read(str);
            // 3、读取结果的数据
            while (Sdr.Read())
            {
                count = Convert.ToInt32(Sdr["totalNumber"]);
                return count;
            }
            return 0;
        }

        public ObservableCollection<Borrow> InquireBorrow(int page, int pageSize)
        {

            ObservableCollection<Borrow> result = new ObservableCollection<Borrow>();

            // 创建要执行的Sql语句， 这里需要联合三张表查询了
            string str = "SELECT ReturnFixture.*, Employee.Name as PersonName, fixture.*,  fixture.Num as fixtureNum FROM " +
                "ReturnFixture JOIN fixture On ReturnFixture.BarCode = fixture.BarCode " +
                "JOIN Employee on Employee.PersonNo = ReturnFixture.PersonNo "
            + $" order by id DESC  LiMIT {pageSize} OFFSET {(page - 1) * pageSize} ;";

            // 2、执行SQL语句并接受结果
            SQLiteDataReader Sdr = sqliteHelper.read(str);

            // 4、读取结果的数据
            while (Sdr.Read())
            {
                result.Add(new Borrow()
                {
                    Id = Convert.ToInt32(Sdr["Id"]),
                    BarCode = Sdr["BarCode"].ToString(), // 条形码
                    Name = Sdr["Name"].ToString(), // 夹具名称
                    MaterialType = Sdr["MaterialType"].ToString(), // 物料类型
                    Num = Convert.ToInt32(Sdr["Num"]), // 借用或归还数量
                    Position = Sdr["Position"].ToString(),
                    PersonNo = Sdr["PersonNo"].ToString(), // 责任人工号
                    PersonName = Sdr["PersonName"].ToString(),  // 责任人姓名
                    UpdateTime = DateTime.Parse(Sdr["UpdateTime"].ToString()),
                    Comment = Sdr["Comment"].ToString(), 
                    Status = Sdr["Status"].ToString(),
                });

            }

            return result;
        }

        public ObservableCollection<Borrow> Search(SearchBorrow em)
        {
            ObservableCollection<Borrow> result = new ObservableCollection<Borrow>();

            string personNo = em.PersonNo;
            string applicationProject = em.ApplicationProject;
            string barcode = em.BarCode;
            DateTime startTime = em.StartTime;
            DateTime endTime = em.EndTime;

            if (string.IsNullOrEmpty(personNo) && string.IsNullOrEmpty(applicationProject) && string.IsNullOrEmpty(barcode) && startTime == null && endTime == null)
                return result;

            // 创建要执行的Sql 语句
            string str = $"SELECT ReturnFixture.*, Employee.Name as PersonName, fixture.*,  fixture.Num as fixtureNum FROM ReturnFixture " +
                         $"Join Employee on  Employee.PersonNo = ReturnFixture.PersonNo " +
                         $" JOIN fixture On ReturnFixture.BarCode = fixture.BarCode Where ";
            List<string> conditions = new List<string>();
            if (!string.IsNullOrEmpty(personNo))
            {
                conditions.Add($" Employee.Name Like '%{personNo}%' ");
            }
            if (!string.IsNullOrEmpty(barcode))
            {
                conditions.Add($" ReturnFixture.Barcode Like '%{barcode}%' ");
            }
            if (startTime != new DateTime(1, 1, 1))
            {
                conditions.Add($" ReturnFixture.UpdateTime >= '{startTime.ToString("yyyy-MM-dd")}'");
            }

            if (endTime != new DateTime(1, 1, 1))
            {
                conditions.Add($" ReturnFixture.UpdateTime <= '{endTime.ToString("yyyy-MM-dd")}'");
            }

            str += string.Join(" AND ", conditions);
            if (conditions.Count == 0)
                return result;
            SQLiteDataReader Sdr = sqliteHelper.read(str);

            int count = 0;
            // 4、读取结果的数据
            while (Sdr.Read())
            {

                result.Add(new Borrow()
                {
                    Id = Convert.ToInt32(Sdr["Id"]),
                    BarCode = Sdr["BarCode"].ToString(), // 条形码
                    Name = Sdr["Name"].ToString(), // 夹具名称
                    MaterialType = Sdr["MaterialType"].ToString(), // 物料类型
                    Num = Convert.ToInt32(Sdr["Num"]), // 借用或归还数量
                    Position = Sdr["Position"].ToString(),
                    PersonNo = Sdr["PersonNo"].ToString(), // 责任人工号
                    PersonName = Sdr["PersonName"].ToString(),  // 责任人姓名
                    UpdateTime = DateTime.Parse(Sdr["UpdateTime"].ToString()),
                    Comment = Sdr["Comment"].ToString(),
                    Status = Sdr["Status"].ToString(),
                });
                count += 1;
            }
            return result;
        }

        public bool Delete(Borrow selectedBorrow)
        {
            string str = $"DELETE FROM ReturnFixture WHERE id = '{selectedBorrow.Id}'; ";
            // 3、执行SQL语句并接受结果
            int r = sqliteHelper.Execute(str);
            return r > 0;
        }

        public bool Update(Borrow selectedBorrow)
        {
            // 创建要执行的Sql 语句 不建议这里修改入库数量或者条码或者工号
            string str = $"UPDATE ReturnFixture SET Num = {selectedBorrow.Num}, PersonNo = '{selectedBorrow.PersonNo}', UpdateTime = '{selectedBorrow.UpdateTime.ToString("yyyy-MM-dd")}', " +
                        $"BarCode = '{selectedBorrow.BarCode}', Status = '{selectedBorrow.Status}', Comment = '{selectedBorrow.Comment}' " +
                        $" WHERE id = {selectedBorrow.Id};";
            // 3、执行SQL语句并接受结果
            int r = sqliteHelper.Execute(str);
            return r > 0;
        }

        public Borrow GetBorrowReturn(int ID)
        {
            Borrow result = new Borrow();

            // 创建要执行的Sql 语句
            string str = $"SELECT ReturnFixture.*, Employee.Name as PersonName, fixture.*,  fixture.Num as fixtureNum FROM ReturnFixture " +
                         $"Join Employee on  Employee.PersonNo = ReturnFixture.PersonNo " +
                         $" JOIN fixture On ReturnFixture.BarCode = fixture.BarCode Where ReturnFixture.Id = {ID}";
            SQLiteDataReader Sdr = sqliteHelper.read(str);
            // 4、读取结果的数据
            while (Sdr.Read())
            {
                result = (new Borrow()
                {
                    Id = Convert.ToInt32(Sdr["Id"]),
                    BarCode = Sdr["BarCode"].ToString(), // 条形码
                    Name = Sdr["Name"].ToString(), // 夹具名称
                    MaterialType = Sdr["MaterialType"].ToString(), // 物料类型
                    Num = Convert.ToInt32(Sdr["Num"]), // 借用或归还数量
                    Position = Sdr["Position"].ToString(),
                    PersonNo = Sdr["PersonNo"].ToString(), // 责任人工号
                    PersonName = Sdr["PersonName"].ToString(),  // 责任人姓名
                    UpdateTime = DateTime.Parse(Sdr["UpdateTime"].ToString()),
                    Comment = Sdr["Comment"].ToString(),
                    Status = Sdr["Status"].ToString(),
                });
            }
            return result;
        }
    }
}
